CREATE OR REPLACE PACKAGE BODY pkg_tcp_svr
AS

	PROCEDURE device_outgoing_done( p_outgoing_logids VARCHAR2 )
	-- p_outgoing_logids format:  dev_outgoing_logid1,S/F;dev_outgoing_logid2,S/F;  
	-- will update status from 'P' to 'S' or 'F'
	-- for 'F', will duplicated same data with status 'N' and created by 'ReSend-'||dtlid
	IS
		CURSOR c_logids IS 
			WITH ids AS ( select p_outgoing_logids logids FROM DUAL )
			SELECT REGEXP_SUBSTR(logids, '[0-9]+', 1, level )  logid
			      ,REGEXP_SUBSTR(logids, '[SF]', 1, level )  status
			FROM ids 
			connect by LEVEL <= REGEXP_COUNT(logids, ',[SF];' )  ;
		
		TYPE log_outgoing_status IS TABLE OF c_logids%ROWTYPE;
		v_logids log_outgoing_status;
		
	BEGIN
		pkg_debug.debug_log( $$PLSQL_UNIT, $$PLSQL_LINE,
							'p_outgoing_logids :'||p_outgoing_logids
							);
		
		OPEN c_logids;
		
		FETCH c_logids BULK COLLECT INTO v_logids;
		
		FORALL i IN v_logids.FIRST .. v_logids.LAST 
			INSERT INTO dev_outgoing_log ( dev_outgoing_logid, device_id, created_by, data_type, outgoing_data, status )
			SELECT  dev_outgoing_log_seq.nextval, device_id, 'ReSend-'||dev_outgoing_logid, data_type, outgoing_data, 'N'
			FROM  dev_outgoing_log 
			WHERE dev_outgoing_logid = v_logids(i).logid AND status = 'P' AND v_logids(i).status = 'F';
			
		FORALL i IN v_logids.FIRST .. v_logids.LAST 
			UPDATE  dev_outgoing_log 
			SET  outgoing_dt = sysdate, status = v_logids(i).status 
			WHERE dev_outgoing_logid = v_logids(i).logid 
				AND status = 'P';
			
		COMMIT;
		
	
	END;
	

	FUNCTION device_outgoing_lists ( p_device_id  VARCHAR2 , p_data_type VARCHAR2 DEFAULT NULL) -- id VARCHAR2)
	RETURN VARCHAR2
	IS
/*
CREATE TABLE dev_outgoing_log(
	 dev_outgoing_logid     NUMBER(15,0)       NOT NULL 
	,device_id              VARCHAR2(15 BYTE)  NOT NULL 
	,create_dt              DATE    DEFAULT SYSDATE NOT NULL  
	,create_by              VARCHAR2(20 Byte)
	,outgoing_dt            DATE    
	,data_type              VARCHAR2(1 BYTE)
	,outgoing_data          VARCHAR2(128 BYTE)
	,status                 VARCHAR2(1 BYTE)
);

下行数据帧内容如下：#;<1>;<2>;<3>; <1> 设备ID号：占用15个字节； <2> 数据类型； <3> 数据；
*/
        v_outmsg VARCHAR2(4000) ;

		
		CURSOR c_outgoing_logs IS 
			SELECT dev_outgoing_logid, device_id, data_type, outgoing_data
			FROM    dev_outgoing_log
			WHERE   device_id = p_device_id
			AND     outgoing_dt IS NULL 
			AND     status IN ( 'N')   --- New 
			FOR UPDATE SKIP LOCKED;

	BEGIN
		
		FOR r IN c_outgoing_logs LOOP

			v_outmsg := v_outmsg||r.dev_outgoing_logid||'~#;'||r.device_id||';'||r.data_type
		               || ';'||r.outgoing_data||';'||' : ';
					   
			UPDATE  dev_outgoing_log 
			SET     status = 'P'  --- Pending 
			WHERE CURRENT OF c_outgoing_logs;
			
		END LOOP;
		
		COMMIT;
		
--		IF  p_data_type IN ('3','4')   THEN 
		-- only when   we send out the time sync data frame.
		     v_outmsg := v_outmsg ||'0~#;'||p_device_id||';1;'||to_char(sysdate, 'yyyy,mm,dd,hh24,mi,ss')||';';
--		END IF;
		
		return  v_outmsg;
	
	END device_outgoing_lists;
	
	PROCEDURE dev_outgoing_lists ( p_device_id  VARCHAR2, p_out_msg OUT VARCHAR2 )	
	IS 
	BEGIN
		p_out_msg := device_outgoing_lists (p_device_id);
	END dev_outgoing_lists;	  

	
	PROCEDURE device_id_merge( p_device_id VARCHAR2, p_ip_addr VARCHAR2, p_port_no NUMBER 
			, p_device_sn OUT NUMBER,  p_pond_id OUT NUMBER )
	IS 
	-- if device_id/ip_addr/p_port_no  exits in table device_list, do nothing
	-- if device_id with different ip_addr/port_no, update ip_addr and log device_ip_hist
	-- if device_id not found, added it.
	
		v_ip_addr   VARCHAR2(15 byte);
		v_port_no   NUMBER (5, 0) ;
		v_ip_match  pls_integer;
		v_port_match pls_integer ;

		v_exp_dt    DATE := sysdate ;
	BEGIN
		p_pond_id := 0;
		p_device_sn := 0;
		
		SELECT  -- ip_addr, port_no,     
				decode( ip_addr, p_ip_addr , 0, 1 ), decode(port_no, p_port_no, 0, 1) 
				, pond_id , device_sn 
			INTO --v_ip_addr, v_port_no, 
				v_ip_match, v_port_match , p_pond_id , p_device_sn 
		FROM   device_list 
		WHERE  device_id = p_device_id ;
	
--		IF v_ip_addr = p_ip_addr AND v_port_no = p_port_no THEN 
		IF v_ip_match  +  v_port_match = 0 THEN 
			NULL;
--		ELSIF v_ip_addr  OR  v_port_no IS NULL THEN
		ELSE 
			UPDATE device_list 
			SET ip_addr = p_ip_addr
			,  port_no  = p_port_no
			WHERE device_id = p_device_id;

			UPDATE device_ip_hist 
			SET exp_dt = v_exp_dt 
			WHERE device_id = p_device_id 
			AND exp_dt IS NULL;
			

			INSERT INTO device_ip_hist (device_sn, device_id, eff_dt, ip_addr, port_no)
			VALUES( p_device_sn, p_device_id, v_exp_dt + 1/86400 , p_ip_addr, p_port_no);

			
		END IF;

	EXCEPTION 
		WHEN NO_DATA_FOUND THEN 
			-- we need add logic to notify management team here
			-- for device id should be ready in table device_list befor we get data from it 
			-- plus, we need set pond_id for the device anyhow 
			
			p_device_sn := device_list_sn_seq.nextval ;
			p_pond_id   := 0;
			
			INSERT INTO device_list ( device_sn, device_id, ip_addr, port_no )
			VALUES( p_device_sn, p_device_id, p_ip_addr, p_port_no);
			
			INSERT INTO device_ip_hist (device_sn, device_id, eff_dt, ip_addr, port_no)
			VALUES( p_device_sn, p_device_id, v_exp_dt + 1/86400 , p_ip_addr, p_port_no);
		
		WHEN OTHERS THEN 
		
			raise;
		
	END device_id_merge;

	FUNCTION str2dec( p_str IN VARCHAR2)
	RETURN NUMBER
	IS
	BEGIN 
		return  to_number( replace(p_str,',') );
	EXCEPTION 
		WHEN OTHERS THEN 
			RETURN 0;
	END str2dec;
	
	PROCEDURE Incoming_split_data(
       p_device_sn     NUMBER
	  ,p_device_id     VARCHAR2
	  ,p_pond_id       NUMBER 
	  ,p_dev_incoming_logid  NUMBER
      ,p_incoming_data VARCHAR2 
	)
	-- p_incoming_data format :
	--  xxx,xxx,xxx,xxx,xxx,xxx,xxxxxxxx
	--
	IS
		v_surface_temperature    NUMBER(4,1)	   ;
		v_deep_temperatur        NUMBER(4,1)	   ;
		v_surface_oxy            NUMBER(4,1)	   ;
		v_deep_oxy               NUMBER(4,1)	   ;
		v_ph_value               NUMBER(4,1)	   ;
		v_other_value            NUMBER(4,1)	   ;
		v_oxy_pole               VARCHAR2(1 BYTE)  ;
		v_surface_water_pump     VARCHAR2(1 BYTE)  ;
		v_deep_water_pump        VARCHAR2(1 BYTE)  ;
		v_ph                     VARCHAR2(1 BYTE)  ;
		v_ammonia_oxy            VARCHAR2(1 BYTE)  ;
		v_other                  VARCHAR2(1 BYTE)  ;
		v_temperature            VARCHAR2(1 BYTE)  ;
		v_power_supply           VARCHAR2(1 BYTE)  ;
		
		v_status                 VARCHAR2(8 BYTE) ;
	BEGIN

		v_surface_temperature  := str2dec(regexp_substr( p_incoming_data, '[^,]*,', 1, 1 ))/10;
		v_deep_temperatur      := str2dec(regexp_substr( p_incoming_data, '[^,]*,', 1, 2 ))/10;
		v_surface_oxy          := str2dec(regexp_substr( p_incoming_data, '[^,]*,', 1, 3 ))/10;
		v_deep_oxy             := str2dec(regexp_substr( p_incoming_data, '[^,]*,', 1, 4 ))/10;
		v_ph_value             := str2dec(regexp_substr( p_incoming_data, '[^,]*,', 1, 5 ))/10;
		v_other_value          := str2dec(regexp_substr( p_incoming_data, '[^,]*,', 1, 6 ))/10;
		v_status               := regexp_substr( p_incoming_data, '[^,]*$');
		v_oxy_pole             := regexp_substr( v_status , '.', 1);
		v_surface_water_pump   := regexp_substr( v_status , '.', 2);
		v_deep_water_pump      := regexp_substr( v_status , '.', 3);
		v_ph                   := regexp_substr( v_status , '.', 4);
		v_ammonia_oxy          := regexp_substr( v_status , '.', 5);
		v_other                := regexp_substr( v_status , '.', 6);
		v_temperature          := regexp_substr( v_status , '.', 7);
		v_power_supply         := regexp_substr( v_status , '.', 8);

	
		MERGE INTO device_data d 
		USING (  SELECT  	
						 p_device_sn            device_sn     
                        ,p_pond_id              pond_id 						 
						,p_dev_incoming_logid   dev_incoming_logid    
						,v_surface_temperature  surface_temperature      
						,v_deep_temperatur      deep_temperature          
						,v_surface_oxy          surface_oxy              
						,v_deep_oxy             deep_oxy                 
						,v_ph_value             ph_value                 
						,v_other_value          other_value              
						,v_oxy_pole             oxy_pole_status          
						,v_surface_water_pump   surface_water_pump_status
						,v_deep_water_pump      deep_water_pump_status   
						,v_ph                   ph_sensor_status         
						,v_ammonia_oxy          ammonia_nitrogen_status       
						,v_other                other_status             
						,v_temperature          temp_status              
						,v_power_supply         power_supply_status    
				FROM DUAL 
		) s 
		ON ( d.device_sn = s.device_sn )
		WHEN MATCHED THEN UPDATE SET 
		                d.dev_incoming_logid           = s.dev_incoming_logid     
					   ,d.pond_id                      = nvl(s.pond_id, d.pond_id)  -- if pond_id is null ,keep it not changed.
		               ,d.surface_temperature          = s.surface_temperature      
		               ,d.deep_temperature             = s.deep_temperature          
		               ,d.surface_oxy                  = s.surface_oxy              
		               ,d.deep_oxy                     = s.deep_oxy                 
		               ,d.ph_value                     = s.ph_value                 
		               ,d.other_value                  = s.other_value              
		               ,d.oxy_pole_status              = s.oxy_pole_status          
		               ,d.surface_water_pump_status    = s.surface_water_pump_status
		               ,d.deep_water_pump_status       = s.deep_water_pump_status   
		               ,d.ph_sensor_status             = s.ph_sensor_status         
		               ,d.ammonia_nitrogen_status      = s.ammonia_nitrogen_status       
		               ,d.other_status                 = s.other_status             
		               ,d.temp_status                  = s.temp_status              
		               ,d.power_supply_status          = s.power_supply_status      
					   ,d.incoming_dt                  = sysdate 
					   
		WHEN NOT MATCHED THEN INSERT (
								 device_sn 
								,device_id             
								,pond_id 
								,dev_incoming_logid    		
								,surface_temperature      		
								,deep_temperature          		
								,surface_oxy              		
								,deep_oxy                 		
								,ph_value                 		
								,other_value              		
								,oxy_pole_status          		
								,surface_water_pump_status		
								,deep_water_pump_status   		
								,ph_sensor_status         		
								,ammonia_nitrogen_status       		
								,other_status             		
								,temp_status              		
								,power_supply_status    	
								,incoming_dt 
								
		     )
		VALUES (  p_device_sn
		         ,p_device_id
		         ,p_pond_id
				 ,p_dev_incoming_logid  
		         ,v_surface_temperature 
		         ,v_deep_temperatur     
		         ,v_surface_oxy         
		         ,v_deep_oxy            
		         ,v_ph_value            
		         ,v_other_value         
		         ,v_oxy_pole            
		         ,v_surface_water_pump  
		         ,v_deep_water_pump     
		         ,v_ph                  
		         ,v_ammonia_oxy         
		         ,v_other               
		         ,v_temperature         
		         ,v_power_supply       
				 ,sysdate 
			) ; 
		
		UPDATE device_data_hist SET exp_dt = SYSDATE 
		WHERE  device_sn  = p_device_sn 
		AND exp_dt IS NULL;
		
		INSERT INTO device_data_hist ( device_sn 
		                              ,device_id                 
		                              ,pond_id 
									  ,dev_incoming_logid        
		                              ,eff_dt                    
		                              ,exp_dt                    
		                              ,surface_temperature       
		                              ,deep_temperature           
		                              ,surface_oxy                
		                              ,deep_oxy                  
		                              ,ph_value                  
		                              ,other_value               
		                              ,oxy_pole_status           
		                              ,surface_water_pump_status 
		                              ,deep_water_pump_status    
		                              ,ph_sensor_status          
		                              ,ammonia_nitrogen_status        
		                              ,other_status              
		                              ,temp_status               
		                              ,power_supply_status       )
		VALUES (    p_device_sn                     
		           ,p_device_id 
		           ,p_pond_id 
				   ,p_dev_incoming_logid            
		           ,sysdate + 1/86400               
		           ,NULL                            
		           ,v_surface_temperature           
		           ,v_deep_temperatur               
		           ,v_surface_oxy                   
		           ,v_deep_oxy                      
		           ,v_ph_value                      
		           ,v_other_value                   
		           ,v_oxy_pole                      
		           ,v_surface_water_pump            
		           ,v_deep_water_pump               
		           ,v_ph                            
		           ,v_ammonia_oxy                   
		           ,v_other                         
		           ,v_temperature                          
		           ,v_power_supply                         
		);
	EXCEPTION 
		WHEN OTHERS THEN 
			RAISE;
		
	END incoming_split_data;


	PROCEDURE Incoming_split_align(
	   p_device_sn     NUMBER 
      ,p_device_id     VARCHAR2
	  ,p_dev_incoming_logid  NUMBER
      ,p_incoming_data VARCHAR2 
	)
	-- p_incoming_data format :
	--  xxx,xxx,xxx,xxx,xxx,xxx,xxxxxxxx
	--
	IS
		v_temperature            device_align.temperature        %type;
		v_oxy_voltage_mv         device_align.oxy_voltage_mv     %type;
		v_oxy_align_ratio        device_align.oxy_align_ratio    %type;
		v_ph_base_voltage_mv     device_align.ph_base_voltage_mv %type;
		v_ph_align_ratio         device_align.ph_align_ratio     %type;
		v_ammonia_oxy            device_align.ammonia_nitrogen        %type;
		
	BEGIN

		v_temperature            := str2dec(regexp_substr( p_incoming_data, '[^,]*', 1, 1 ))/10;
		v_oxy_voltage_mv         := str2dec(regexp_substr( p_incoming_data, '[^,]*', 1, 2 ));
		v_oxy_align_ratio        := str2dec(regexp_substr( p_incoming_data, '[^,]*', 1, 3 ));
		v_ph_base_voltage_mv     := str2dec(regexp_substr( p_incoming_data, '[^,]*', 1, 4 ));
		v_ph_align_ratio         := str2dec(regexp_substr( p_incoming_data, '[^,]*', 1, 5 ));
		v_ammonia_oxy            := str2dec(regexp_substr( p_incoming_data, '[^,]*', 1, 6 ));
		
		
		MERGE INTO device_align d 
		USING (  SELECT  	
						 p_device_sn            device_sn     
						,p_dev_incoming_logid   dev_incoming_logid    
						,v_temperature          temperature           
						,v_oxy_voltage_mv       oxy_voltage_mv        
						,v_oxy_align_ratio      oxy_align_ratio       
						,v_ph_base_voltage_mv   ph_base_voltage_mv    
						,v_ph_align_ratio       ph_align_ratio        
						,v_ammonia_oxy          ammonia_nitrogen           
				FROM DUAL 
		) s 
		ON ( d.device_sn  = s.device_sn )
		WHEN MATCHED THEN UPDATE SET 
		                d.dev_incoming_logid       = s.dev_incoming_logid     
		               ,d.temperature              = s.temperature          
		               ,d.oxy_voltage_mv           = s.oxy_voltage_mv       
		               ,d.oxy_align_ratio          = s.oxy_align_ratio      
		               ,d.ph_base_voltage_mv       = s.ph_base_voltage_mv   
		               ,d.ph_align_ratio           = s.ph_align_ratio       
		               ,d.ammonia_nitrogen              = s.ammonia_nitrogen          
		WHEN NOT MATCHED THEN INSERT (
								 device_sn
								,device_id 
								,dev_incoming_logid   
								,temperature          
								,oxy_voltage_mv       
								,oxy_align_ratio      
								,ph_base_voltage_mv   
								,ph_align_ratio       
								,ammonia_nitrogen          
		     )
		VALUES (  p_device_sn           
				 ,p_device_id           
				 ,p_dev_incoming_logid  
		         ,v_temperature          
		         ,v_oxy_voltage_mv       
		         ,v_oxy_align_ratio      
		         ,v_ph_base_voltage_mv   
		         ,v_ph_align_ratio       
		         ,v_ammonia_oxy          
			) ; 
		
		UPDATE device_align_hist SET exp_dt = SYSDATE 
		WHERE  device_sn  = p_device_sn
		AND exp_dt IS NULL;
		
		INSERT INTO device_align_hist ( device_sn                 
									  ,device_id
									  ,dev_incoming_logid        
		                              ,eff_dt
									  ,exp_dt
		                              ,temperature          
		                              ,oxy_voltage_mv       
		                              ,oxy_align_ratio      
		                              ,ph_base_voltage_mv   
		                              ,ph_align_ratio       
		                              ,ammonia_nitrogen          
									)
		VALUES (    p_device_sn                     
				   ,p_device_id
				   ,p_dev_incoming_logid            
		           ,sysdate + 1/86400               
		           ,NULL                            
		           ,v_temperature                
		           ,v_oxy_voltage_mv             
		           ,v_oxy_align_ratio            
		           ,v_ph_base_voltage_mv         
		           ,v_ph_align_ratio             
		           ,v_ammonia_oxy                
		);
	EXCEPTION 
		WHEN OTHERS THEN 
			RAISE;
		
	END Incoming_split_align;	


	PROCEDURE Incoming_split_threshold(
       p_device_sn     NUMBER
      ,p_device_id     VARCHAR2
	  ,p_dev_incoming_logid  NUMBER
      ,p_incoming_data VARCHAR2 
	)
	-- p_incoming_data format :
	--  xxx,xxx,xxx,xxx,xxx,xxx,xxxxxxxx
	--
	IS
		v_oxy_min            device_sensor_threshold.oxy_min         %type;
		v_oxy_max            device_sensor_threshold.oxy_max         %type;
		v_ph_min             device_sensor_threshold.ph_min          %type;
		v_ph_max             device_sensor_threshold.ph_max          %type;
		v_ammonia            device_sensor_threshold.ammonia_nitrogen%type;
		v_temperature_min    device_sensor_threshold.temperature_min %type;
		
	BEGIN

		v_oxy_min            := str2dec(regexp_substr( p_incoming_data, '[^,]*,', 1, 1 ))/10;
--		v_oxy_max            := str2dec(regexp_substr( p_incoming_data, '[^,]*,', 1, 2 ))/10;
		v_ph_min             := str2dec(regexp_substr( p_incoming_data, '[^,]*,', 1, 2 ))/10;
		v_ph_max             := str2dec(regexp_substr( p_incoming_data, '[^,]*,', 1, 3 ))/10;
		v_ammonia            := str2dec(regexp_substr( p_incoming_data, '[^,]*,', 1, 4 ))/10;
		v_temperature_min    := str2dec(regexp_substr( p_incoming_data, '[^,]*,', 1, 5 ))/10;
		                                                                  
		
		MERGE INTO device_sensor_threshold d 
		USING (  SELECT  	
						 p_device_sn            device_sn     
						,p_dev_incoming_logid   dev_incoming_logid    
						,v_oxy_min              oxy_min            
						,v_oxy_max              oxy_max            
						,v_ph_min               ph_min             
						,v_ph_max               ph_max             
						,v_ammonia              ammonia_nitrogen            
						,v_temperature_min      temperature_min    
				FROM DUAL 
		) s 
		ON ( d.device_sn = s.device_sn )
		WHEN MATCHED THEN UPDATE SET 
		                d.dev_incoming_logid   = s.dev_incoming_logid     
		               ,d.oxy_min              = s.oxy_min        
		               ,d.oxy_max              = s.oxy_max        
		               ,d.ph_min               = s.ph_min         
		               ,d.ph_max               = s.ph_max         
		               ,d.ammonia_nitrogen     = s.ammonia_nitrogen        
		               ,d.temperature_min      = s.temperature_min
		WHEN NOT MATCHED THEN INSERT (
								 device_sn             
								,device_id             
								,dev_incoming_logid   
								,oxy_min        
								,oxy_max        
								,ph_min         
								,ph_max         
								,ammonia_nitrogen        
								,temperature_min
		     )
		VALUES (  p_device_sn           
				 ,p_device_id 
				 ,p_dev_incoming_logid  
		         ,v_oxy_min        
		         ,v_oxy_max        
		         ,v_ph_min         
		         ,v_ph_max         
		         ,v_ammonia        
		         ,v_temperature_min
			) ; 
		
		UPDATE device_sensor_threshold_hist SET exp_dt = SYSDATE 
		WHERE  device_sn  = p_device_sn 
		AND exp_dt IS NULL;
		
		INSERT INTO device_sensor_threshold_hist (
                                		device_sn
									  ,device_id 
									  ,dev_incoming_logid        
		                              ,eff_dt
									  ,exp_dt
		                              ,oxy_min         
		                              ,oxy_max         
		                              ,ph_min          
		                              ,ph_max          
		                              ,ammonia_nitrogen         
		                              ,temperature_min 
									)
		VALUES (    p_device_sn                     
				   ,p_device_id
				   ,p_dev_incoming_logid            
		           ,sysdate + 1/86400               
		           ,NULL                            
		           ,v_oxy_min            
		           ,v_oxy_max            
		           ,v_ph_min             
		           ,v_ph_max             
		           ,v_ammonia            
		           ,v_temperature_min    
		);
	EXCEPTION 
		WHEN OTHERS THEN 
			RAISE;
		
	END Incoming_split_threshold;	

/*

CREATE TABLE device_version_hist (
	 device_id               VARCHAR2(15 BYTE)   NOT NULL
	,dev_incoming_logid	     NUMBER(15,0)        NOT NULL
	,eff_dt                  DATE                NOT NULL
	,exp_dt                  DATE
	,main_controller_hw      VARCHAR2(13 BYTE)
	,main_controller_sw      VARCHAR2(13 BYTE)
	,float_dev_id            VARCHAR2(15 BYTE)
	,float_dev_hw            VARCHAR2(13 BYTE)
	,float_dev_sw            VARCHAR2(13 BYTE)
 );
*/


	PROCEDURE Incoming_split_version(
       p_device_sn     NUMBER 	
      ,p_device_id     VARCHAR2
	  ,p_dev_incoming_logid  NUMBER
      ,p_incoming_data VARCHAR2 
	)
	-- p_incoming_data format :
	--  xxx,xxx,xxx,xxx,xxx,xxx,xxxxxxxx
	--
	IS
		v_main_controller_hw   device_version.main_controller_hw  %type;
		v_main_controller_sw   device_version.main_controller_sw  %type;
		v_float_dev_id         device_version.float_dev_id        %type;
		v_float_dev_hw         device_version.float_dev_hw        %type;
		v_float_dev_sw         device_version.float_dev_sw        %type;
		
	BEGIN
	
		v_main_controller_hw  := regexp_substr( p_incoming_data, '[0-9A-Z]{7,13}', 1, 1 );
		v_main_controller_sw  := regexp_substr( p_incoming_data, '[0-9A-Z]{7,13}', 1, 2 );
		v_float_dev_id        := regexp_substr( p_incoming_data, '[0-9A-Z]{7,15}', 1, 3 );
		v_float_dev_hw        := regexp_substr( p_incoming_data, '[0-9A-Z]{7,13}', 1, 4 );
		v_float_dev_sw        := regexp_substr( p_incoming_data, '[0-9A-Z]{7,13}', 1, 5 );
		                                                                  
		
		MERGE INTO device_version d 
		USING (  SELECT  	
						 p_device_sn            device_sn     
						,p_dev_incoming_logid   dev_incoming_logid    
						,v_main_controller_hw   main_controller_hw          
						,v_main_controller_sw   main_controller_sw          
						,v_float_dev_id         float_dev_id                
						,v_float_dev_hw         float_dev_hw                
						,v_float_dev_sw         float_dev_sw                
				FROM DUAL 
		) s 
		ON ( d.device_sn = s.device_sn )
		WHEN MATCHED THEN UPDATE SET 
		                d.dev_incoming_logid   = s.dev_incoming_logid     
		               ,d.main_controller_hw   = s.main_controller_hw
		               ,d.main_controller_sw   = s.main_controller_sw
		               ,d.float_dev_id         = s.float_dev_id      
		               ,d.float_dev_hw         = s.float_dev_hw      
		               ,d.float_dev_sw         = s.float_dev_sw      
		WHEN NOT MATCHED THEN INSERT (
								 device_sn 
								,device_id             
								,dev_incoming_logid   
								,main_controller_hw
								,main_controller_sw
								,float_dev_id      
								,float_dev_hw      
								,float_dev_sw      
		     )
		VALUES (  p_device_sn          
		         ,p_device_id           
				 ,p_dev_incoming_logid  
		         ,v_main_controller_hw
		         ,v_main_controller_sw
		         ,v_float_dev_id      
		         ,v_float_dev_hw      
		         ,v_float_dev_sw      
			) ; 
		
		UPDATE device_version_hist SET exp_dt = SYSDATE 
		WHERE  device_sn  = p_device_sn
		AND exp_dt IS NULL;
		
		INSERT INTO device_version_hist ( device_sn
		                              ,device_id                 
									  ,dev_incoming_logid        
		                              ,eff_dt
									  ,exp_dt
		                              ,main_controller_hw
		                              ,main_controller_sw
		                              ,float_dev_id      
		                              ,float_dev_hw      
		                              ,float_dev_sw      
									)
		VALUES (    p_device_sn 
		           ,p_device_id                     
				   ,p_dev_incoming_logid            
		           ,sysdate + 1/86400               
		           ,NULL                            
		           ,v_main_controller_hw
		           ,v_main_controller_sw
		           ,v_float_dev_id      
		           ,v_float_dev_hw      
		           ,v_float_dev_sw      
		);
	EXCEPTION 
		WHEN OTHERS THEN 
			RAISE;
		
	END Incoming_split_version;	

	PROCEDURE Incoming_split(
       p_device_sn     NUMBER 
	  ,p_device_id     VARCHAR2
	  ,p_pond_id       NUMBER 
	  ,p_dev_incoming_logid  NUMBER
      ,p_data_type     VARCHAR2
      ,p_incoming_data VARCHAR2 
	)
	IS 

	BEGIN
		
		CASE p_data_type 	
		  WHEN '1' THEN Incoming_split_data     (p_device_sn, p_device_id, p_pond_id, p_dev_incoming_logid, p_incoming_data);
		  WHEN '3' THEN Incoming_split_align    (p_device_sn, p_device_id, p_dev_incoming_logid, p_incoming_data);
		  WHEN '4' THEN Incoming_split_version  (p_device_sn, p_device_id, p_dev_incoming_logid, p_incoming_data);
		  WHEN '5' THEN Incoming_split_threshold(p_device_sn, p_device_id, p_dev_incoming_logid, p_incoming_data);
		  WHEN '6' THEN  NULL; -- heart beat 
		  WHEN 'A' THEN  NULL; -- Incoming_split_data( p_device, p_dev_incoming_logid, p_incoming_data);
		END CASE;
	EXCEPTION 
		WHEN OTHERS THEN 
			RAISE ;
	
	END Incoming_split;
	
    PROCEDURE Incoming_data (
       p_device_id     VARCHAR2
      ,p_data_type     VARCHAR2
      ,p_incoming_data VARCHAR2 
	  ,p_ip_addr       VARCHAR2
	  ,p_port_no       NUMBER
	  ,p_outgoing_msgs OUT  VARCHAR2      
      )
	IS
		v_incoming_dt  DATE ;
		v_incoming_logid dev_incoming_log.dev_incoming_logid%TYPE;
		v_outgoing_msg VARCHAR2(4000);
		v_device_id  device_list.device_id%type ;
		v_data_type  VARCHAR2(1 Byte):=trim(p_data_type);
		v_pond_id   NUMBER (9,0) := 0 ;
		v_device_sn number(9,0) := 0;

	BEGIN
		dbms_output.put_line(' P_device_id:'||p_device_id||', len(p_device_id)='||length(p_device_id));
		v_device_id := trim( p_device_id) ;
		
		device_id_merge( v_device_id, p_ip_addr, p_port_no, v_device_sn, v_pond_id );
		
	
		INSERT INTO dev_incoming_log (dev_incoming_logid, device_sn,  device_id, pond_id, data_type, incoming_data) 
			VALUES (dev_incoming_log_seq.nextval, v_device_sn, v_device_id, v_pond_id, v_data_type, p_incoming_data ) 
		RETURN incoming_dt, dev_incoming_logid  INTO v_incoming_dt, v_incoming_logid;

		COMMIT; -- now we already save the data 
		        -- next step we split the data into different tables
		incoming_split( v_device_sn, v_device_id, v_pond_id, v_incoming_logid, v_data_type, p_incoming_data);
		
		COMMIT;

		
		v_outgoing_msg := device_outgoing_lists(v_device_id, v_data_type );
		p_outgoing_msgs:= v_outgoing_msg;
		
	EXCEPTION 
		WHEN OTHERS THEN 
			v_outgoing_msg :=  DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()
			                  ||chr(10)||chr(13)||'================='||chr(10)||chr(13)||
							DBMS_UTILITY.FORMAT_ERROR_STACK ()	;
			p_outgoing_msgs := v_outgoing_msg;
			
	END Incoming_data ;

BEGIN
	pkg_debug.SetTopic('PKG_TCP_SVR');
			  		
END pkg_tcp_svr;
/

--grant execute on pkg_tcp_svr to xxx;

